package product;

import helper.CacheCallBack;
import helper.CacheHelper;
import models.BaseModel;
import models.admin.AdminUser;
import models.constants.DeletedStatus;
import models.merchant.Merchant;
import order.AdminOrderItem;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import product.enums.DisableStatus;
import product.enums.ProductClassify;
import util.common.ConvertUtil;
import util.common.UStringUtil;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 产品型号
 * Created by buhaoba on 16/6/14.
 */
@Entity
@Table(name = "product_specs")
public class ProductSpec extends BaseModel {

    public static final String CACHE_PRODUCTSPEC_ID = "ULC_CACHE_PRODUCTSPEC_ID_";
    /**
     * 产品信息
     */
    @JoinColumn(name = "product_id")
    @ManyToOne
    public Product product;

    /**
     * * 型号
     */
    @Column(name = "spec")
    public String spec;

    /**
     * 单位
     */
    @Column(name = "unit")
    public String unit;

    /**
     * 型号附带金额 (在 product.type = RECHARGEABLE_CARD 时生效)
     */
    @Column(name = "amount")
    public Double amount;

    /**
     * 型号赠送金额 (在 product.type = RECHARGEABLE_CARD 时生效)
     */
    @Column(name = "give_amount")
    public Double giveAmount;

    /**
     * 主图 在切换型号的时候展示
     */
    @Column(name = "main_image")
    public String mainImage;

    /**
     * 商户
     */
    @Transient
    public Merchant merchant;

    /**
     * 编号
     */
    @Column(name = "d_code")
    public String dCode;

    /**
     * 条形码
     */
    @Column(name = "bar_code")
    public String barCode;

    /**
     * 是否已拆分
     */
    @Column(name="isSplit")
    public Boolean isSplit;
    /**
     * 箱规
     */
    @Column(name = "boxNum")
    public Double boxNum;

    /**
     * 产品分类 BASIC_PRODUCT:基础产品 FINISHED_PRODUCT:成品 ACCESSORY:辅料 COMBO_PRODUCT:套餐
     */
    @Enumerated(EnumType.STRING)
    @Column(name = "product_classify")
    public ProductClassify productClassify;

    /**
     * 逻辑删除,0:未删除，1:已删除
     */
    @Enumerated(EnumType.ORDINAL)
    public DeletedStatus deleted;

    /**
     * 是否审核
     */
    @Column(name = "is_audit")
    public Boolean isAudit;


    //审核日期
    @Column(name = "audit_at")
    @Temporal(TemporalType.TIMESTAMP)
    public Date auditAt;

    //审核人
    @ManyToOne
    @JoinColumn(name = "auditor_id")
    public AdminUser auditor;

    /**
     * 使用状态
     * DISABLE : 禁用   ENABLE : 启用
     */
    @Enumerated(EnumType.STRING)
    @Column(name = "disable_status")
    public DisableStatus disableStatus = DisableStatus.ENABLE;

    /**
     * 颜色标记
     */
    @Column(name = "color_mark")
    public String colorMark;

    /**
     * 库存警戒数量
     */
    @Column(name = "warning_number")
    public Double warningNumber;

    /**
     * 产品重量
     */
    @Column(name = "weight")
    public Double weight;

    /**
     * 备注
     */
    @Column(name = "remark")
    public String remark;

    /**
     * 可否退货
     */
    @Column(name = "can_return")
    public Boolean canReturn;

    @Transient
    public String createAtStr;

    @Transient
    public Double stockNum;

    @Transient
    public Double costPrice;

    /**
     * 从缓存中根据ID查实体
     * @param id
     * @return
     */
    public static ProductSpec findByCacheId(final long id) {
        return CacheHelper.getCache(CACHE_PRODUCTSPEC_ID+id, new CacheCallBack<ProductSpec>() {
            @Override
            public ProductSpec loadData() {
                return ProductSpec.find("deleted = ? and id = ?", DeletedStatus.UN_DELETED, id).first();
            }
        });
    }
    /**
     * 根据产品ID,判断产品下是否有产品规格
     *
     * @param id
     * @return
     */
    public static Boolean checkProductSpec(long id) {
        return ProductSpec.count("product.id = ? and deleted = ? ",id,DeletedStatus.UN_DELETED)>0;
    }

    public static List<ProductSpec> loadByProductId(long productId) {
        return ProductSpec.find("product.id=? and deleted=? order by disableStatus desc", productId, DeletedStatus.UN_DELETED).fetch();
    }

    public List<ProductSpecImage> getProductSpecImage() {
        return ProductSpecImage.find(" deleted = ? and productSpec.id = ?", DeletedStatus.UN_DELETED, this.id).fetch();
    }

    /**
     * 根据根据ProductSpec的ID查询ProductSpec
     */
    public static ProductSpec findByProSpecId(long id) {
        return ProductSpec.find("id=? and deleted=?", id, DeletedStatus.UN_DELETED).first();
    }
    /**
     * 判断某规格下是否有拆分明细
     */
    public static Boolean checkIsSplit(long id){
       return ProductSpecSplit.count("comboProductSpec.id=?",id)>0;
    }

    /**
     * 生成产品规格编号
     */
    public static String createDCode(Product product) {
        long number = ProductSpec.count("product.id=?", product.id) + 1;
        return number>=100? ConvertUtil.toString(product.code)+number:ConvertUtil.toString(product.code)+ConvertUtil.toString(100+number).substring(1);
    }

    public static String checkIsUsed(Integer[] delIds){
         String delIdStr= UStringUtil.concatStr(",",delIds);
        String str="";
        String sqlSelect="select concat(t4.name,t2.spec) as name " +
                " FROM product_spec_split t1" +
                " left join product_specs t2 on t1.split_product_spec_id  = t2.id " +
                " left join product_specs t3 on t1.combo_product_spec_id  = t3.id" +
                " left join products t4 on t2.product_id  = t4.id" +
                " where t2.id in ("+delIdStr+") and t3.`deleted`  = 0" +
                " group by t4.id,t4.name,t2.id ,t2.spec; ";
        List<Map<String,Object>> resultList=new ArrayList<>();
        Query query=ProductSpec.em().createNativeQuery(sqlSelect);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        resultList=query.getResultList();
        System.out.println(ConvertUtil.toJson(resultList));
        if(resultList.size()>0){
            for(Map<String,Object> product: resultList){
                str +=ConvertUtil.toString(product.get("name"))+",";
            }
        }
        return str;
    }


    //产品某规格的销量
    public final static String PRODUCT_SPEC_SALE_NUM =
            " select a.product_spec_id, " +
                    " sum(a.buy_number) as spec_sale_num " +
                    " from  " +
                    " (select   " +
                    " f.id as product_spec_id ," +
                    "  a.buy_number, " +
                    " a.deleted " +
                    " from admin_order_item a " +
                    " left join admin_order b on b.id = a.admin_order " +
                    " left join goods c on a.goods_id = c.id " +
                    " left join product_spec_merchants e on c.serial_id = e.id " +
                    " left join product_specs f on e.product_spec_id = f.id " +
                    " where a.deleted = 0 and b.to_track = 1  and b.deleted = 0 " +
                    " ) a %s group by a.product_spec_id";


    /**
     *查询某产品的销售量
     * @param id
     * @return
     */
    public static Double countProductSpecSaleNum(long id){
        String sqlSelect = String.format(PRODUCT_SPEC_SALE_NUM,"where a.product_spec_id = "+id);
        Query query = AdminOrderItem.em().createNativeQuery(sqlSelect);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String,Object>> resultList = query.getResultList();
        if(resultList.size() >0)
            return ConvertUtil.toDouble(resultList.get(0).get("spec_sale_num"));
        else
            return 0D;

    }

    //禁用/启用产品规格
    public static void disableSpec(Integer[] ids,DisableStatus operat){
        String idStr = UStringUtil.concatStr(",",ids);
        String sqlSelect = " update product_specs a set a.disable_status = '"+operat+"'  where a.id in ("+idStr+") ";
        em().createNativeQuery(sqlSelect).executeUpdate();
    }

}
